package com.gy.hadoop.hive;

/*
    CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
    [(col_name data_type [COMMENT col_comment], ...)]
    [COMMENT table_comment]
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 分区
    [CLUSTERED BY (col_name, col_name, ...) 分桶
    [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
    [ROW FORMAT row_format]   row format delimited fields terminated by “分隔符”
    [STORED AS file_format]
    [LOCATION hdfs_path]

1.简述hive工作原理：
    sql执行工具
    将用户sql解析成rm任务在hadoop上执行
    内部有解析器（sql解析，校验），编译器（sql编译成rm程序），优化器（执行单元的优化），执行器（发布到hadoop集群中运行）
2.hive 内部表和外部表区别
    内部表删除表时表中的数据也同时被删除，外部表删除表只会删除元数据。外部表更使用存储底层数据，更好安全性。
3.有以下格式的数据创建出合适的表结构，并将数据导入表中
    战狼2,吴京:吴刚:卢婧姗,2017-08-16
    大话西游,周星驰:吴孟达,1995-09-01
    哪吒,吕艳婷:瀚墨,2019-07-26
    使徒行者2,张家辉:古天乐:吴镇宇,2019-08-07
    鼠胆英雄,岳云鹏:佟丽娅:田雨:袁弘,2019-08-02
    create external table if not exists movie (
        movie string,
        actor array<String>,
        idate date
    )
    row format delimited fields terminated by ','
    collection items terminated by ':'
    stored as textfile
    location '/user/external/output/movie.txt'

    **3.1 查询出每个电影的第二个主演**
        select t1.actor[1] from movie as t1
    **3.2 查询每部电影有几名主演**
        select movie,size(t1.actor) from movie as t1
    **3.3主演里面包含古天乐的电影**
        select movie,t1.actor from movie as t1 where array_contains(t1.actor,"古天乐")

4.有以下格式的数据创建出合适的表结构，并将数据导入表中
    1,张三,18:male:北京
    2,李四,29:female:上海
    3,杨朝来,22:male:深圳 
    4,蒋平,34:male:成都
    5,唐灿华,25:female:哈尔滨
    6,马达,17:male:北京
    7,赵小雪,23:female:杭州
    8,薛文泉,26:male:上海
    9,丁建,29:male:北京

    create external table if not exists user_info (
        id int,
        username varchar(150),
        user_info struct<age:int,gender:varchar(50),address:varchar(100)>
    )
    row format delimited fields terminated by ','
    collection items terminated by ':'
    stored as textfile
    location '/user/external/output/user_info.txt'

    **4.1 查询出每个人的id,名字,居住地址**
        select t1.id,t1.username,t1.user_info.address from user_info as t1

5.有以下格式的数据创建出合适的表结构，并将数据导入表中
    1,小明,father:张三#mother:李丽#brother:小力,28
    2,小华,father:李四#mother:吴姗#sister:小静,16
    3,Aaron,father:Abbot#mother:Abby#brother:Abner,18
    4,张启然,father:张雨勇#mother:蒋箐#sister:张业丽,21
    5,马钰林,father:马洪思#mother:郑小涵#brother:马靖文,27
    6,曾畅,father:曾文来#mother:房彤媛#sister:曾婉沁,21
    7,韩文尧,father:韩石泉#mother:郑红珊#sister:韩妤,25
    8,Dailey,father:Eddie#mother:Lacey#brother:Karla,18
    create external table if not exists t_user (
        id int,
        uname varchar(150),
        ref map<varchar(100),varchar(120)>
    )
    row format delimited fields terminated by ','
    collection items terminated by '#'
    map  keys terminated by ':'
    stored as textfile
    location '/user/external/output/user'

    **5.1 查看每个人的父亲**
        select t1.uname,t1.ref['father'] from t_user as t1;
    **5.2 每个人有哪些亲属关系**
        select t1.uname,map_keys(t1.ref) as f from t_user as t1;
    **5.3 查出每个人的亲人名字**
        select t1.uname,map_values(t1.ref) as f from t_user as t1;
    **5.4 查出每个人的亲人数量**
        select t1.uname,size(t1.ref) as f from t_user as t1;
    **5.5 查出所有拥有兄弟的人的id和名字**
        select t1.id,t1.uname from t_user as t1 where array_contains(map_keys(t1.ref),cast('brother' as varchar(100)));
 */
public class DemoMain {

    public static void main(String[] args) {

    }
}
